package Stock.StockDAOS;

import java.sql.*;
import project.*;
import java.awt.event.*;
import java.awt.*;
import javax.swing.*;
import javax.swing.event.*;
import java.util.*;
import module4.*;
import MainFunctions.MainFunctionsDAOS.*;
import Password.*;
import project.*;


public class StockNewManager 
{
	String myDriver = "com.mysql.jdbc.Driver";
	String ipOfDB = "localhost";
	MySQLdriverConnection msc;
	Connection Conn;
	private String input=null, type=null;
	StockNew stock; 
	private Table table = null;
	private JTable jTable = null;
	
			String queryStockID = "SELECT stockId, stockTypeId, title,"+ 
							"status, barcode, brand, serialNo, description,"+ 
							"catId, runTime, releaseDate, genre, age,"+ 
							"director, actor1, actor2, dateLastRented"+ 
							" FROM stockTable "+ 
							"WHERE stockId LIKE ?";
			String queryTitle = "SELECT stockId, stockTypeId, title,"+ 
							"status, barcode, brand, serialNo, description,"+ 
							"catId, runTime, releaseDate, genre, age,"+ 
							"director, actor1, actor2, dateLastRented"+ 
							" FROM stockTable "+ 
							"WHERE title LIKE ?";
			String queryBarcode = "SELECT stockId, stockTypeId, title,"+ 
							"status, barcode, brand, serialNo, description,"+ 
							"catId, runTime, releaseDate, genre, age,"+ 
							"director, actor1, actor2, dateLastRented"+ 
							" FROM stockTable "+ 
							"WHERE barcode LIKE ?";
			static String UPDATE_STOCK = "UPDATE stockTable SET stockId=?, "+
					"stockTypeId=?,title=?,status=?,barcode=?,"+
					"brand=?, serialNo=?, description=?,"+
					"catId=?, runTime=?, releaseDate=?, genre=?,"+
					"age=?, director=?, actor1=?, actor2=?,"+
					"dateLastRented=? WHERE stockId = ?";	
					
			public static String DELETE_STOCK = 
					"DELETE FROM stockTable WHERE stockId = ?";		
		
			public static String GET_STOCK_BY_TITLE = 
					"SELECT stockId, stockTypeId, title, status, barcode, brand" +
					" FROM stockTable WHERE title LIKE ?";
			public static String GET_STOCK_BY_BARCODE = 
					"SELECT stockId, stockTypeId, title, status, barcode, brand" +
					" FROM stockTable WHERE barcode LIKE ?";
			public static String GET_STOCK_BY_STOCKID =
					"SELECT stockId, stockTypeId, title, status, barcode, brand" +
					" FROM stockTable WHERE stockId LIKE ?";
			String queryStockType =	"SELECT stockTypeId FROM stockTypeTable WHERE stockTypeName = ?";
			String queryCatId =	"SELECT catId FROM priceCatTable WHERE catName = ?";								
	
	public StockNewManager(Connection Conn) 
	{
		this.Conn = Conn;
		
	}
	
	//used to commit stock when adding new stock items and 
	//editing stock items, saving any changes.
	public void commitStock(StockNew stock)
	{
		try
		{
			this.stock = stock;
			String [] str = stock.getStockString();
			Statement stmt = Conn.createStatement();
			String update;
			for(int i = 0; i < str.length; i++)
			{
				System.out.println(str[i]);
			}
			
			update = "INSERT INTO stockTable VALUES('"+
				str[0]+"','"+str[1]+"','"+str[2]+"','"+str[3]+"','"+
				str[4]+"','"+str[5]+"','"+str[6]+"','"+str[7]+"','"+
				str[8]+"','"+str[9]+"','"+str[10]+"','"+str[11]+"','"+
				str[12]+"','"+str[13]+"','"+str[14]+"','"+
				str[15]+"', '"+str[16]+"')";
			int rows = stmt.executeUpdate(update);
			System.out.println("Rows changed: "+rows);
			
		}
		catch(SQLException sqlx)
		{
			sqlx.printStackTrace();
		}
	}	
	//searchStock returns a Jtable when searching for stock items and the resultset is
	//greater that one result.
	public JTable searchStock(String inputString, String critString)
		{
			String input = inputString;
			String criteria = critString;
			try
			{
				if(criteria.equals("Title"))
				{
					ResultSet rs;
					PreparedStatement ps = Conn.prepareStatement(
						GET_STOCK_BY_TITLE,
						ResultSet.TYPE_SCROLL_SENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
					ps.setString(1, input +'%');
					rs = ps.executeQuery();
					table = new Table();
					jTable = table.createStockSearchTable(rs);
				}
				else if(criteria.equals("Stock ID"))
				{
					ResultSet rs;
					PreparedStatement ps = Conn.prepareStatement(
						GET_STOCK_BY_STOCKID,
						ResultSet.TYPE_SCROLL_SENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
					ps.setString(1, input +'%');
					rs = ps.executeQuery();
					table = new Table();
					jTable = table.createStockSearchTable(rs);
				}
				else
				{
					ResultSet rs;
					PreparedStatement ps = Conn.prepareStatement(
						GET_STOCK_BY_BARCODE,
						ResultSet.TYPE_SCROLL_SENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
					ps.setString(1, input +'%');
					rs = ps.executeQuery();
					table = new Table();
					jTable = table.createStockSearchTable(rs);
				}
				

			}
			catch(SQLException sqle)
			{
				System.out.println(sqle.getMessage());
			}
			return jTable;
		}
		
	//findStock returns a single stock item, used when selecting from a Jtable	
	public StockNew findStock(String inString, String typeString)
	{
		String input = inString;
		String type = typeString;
		try
		{
			PreparedStatement pstmt;
			ResultSet rs;
			if(type.equals("Stock ID"))
			{
				pstmt = Conn.prepareStatement(
						queryStockID);
				System.out.println("Stock ID");
				pstmt.setString(1, input +"%");
				rs = pstmt.executeQuery();
			}
			else if(type.equals("Barcode"))
			{
				pstmt = Conn.prepareStatement(
						queryBarcode);
				System.out.println("Barcode");
				pstmt.setString(1, input +"%");
				rs = pstmt.executeQuery();
			}
			else if(type.equals("Title"))
			{
				System.out.println("in title = ");
				pstmt = Conn.prepareStatement(
						queryTitle);
				System.out.println("Title");
				pstmt.setString(1, input +"%");
				rs = pstmt.executeQuery();
			}
			else
			{
				pstmt = Conn.prepareStatement(
						queryStockID);
				System.out.println("Stock ID");
				pstmt.setString(1, input +"%");
				rs = pstmt.executeQuery();
			}
			
			if(rs.next())
			{
				rs.first();
				String [] queryArray = new String [17];
				queryArray[0] = rs.getString("stockId");
				queryArray[1] = rs.getString("stockTypeId");
				queryArray[2] = rs.getString("title");
				queryArray[3] = rs.getString("status");
				queryArray[4] = rs.getString("barcode");
				queryArray[5] = rs.getString("brand");
				queryArray[6] = rs.getString("serialNo");
				queryArray[7] = rs.getString("description");
				queryArray[8] = rs.getString("catId");
				queryArray[9] = rs.getString("runTime");
				queryArray[10] = rs.getString("releaseDate");
				queryArray[11] = rs.getString("genre");
				queryArray[12] = rs.getString("age");
				queryArray[13] = rs.getString("director");
				queryArray[14] = rs.getString("actor1");
				queryArray[15] = rs.getString("actor2");
				queryArray[16] = rs.getString("dateLastRented");
				
				StockNew s = new StockNew(queryArray);
		
				return s;
			}
			else
			{
				return null;
			}
		}
		catch(SQLException sqlx)
		{
			sqlx.printStackTrace();
			return null;
		}
	}
	//generateStockId checks the db for the last stock item and then 
	//increments that stock id. Used when adding new stock item.
	public String generateStockId()
	{
		String uniqueId = null;
		try
		{
			Statement stockIdGeneratorSTMT = Conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
			ResultSet stockIdGeneratorRS;
			String stockIdGeneratorQUERY = "SELECT stockId FROM stockTable";
		
			stockIdGeneratorRS = stockIdGeneratorSTMT.executeQuery(stockIdGeneratorQUERY);
		
			stockIdGeneratorRS.last();
			String lastId = stockIdGeneratorRS.getString(1);
			System.out.println("Previous id = "+ lastId);
			String tempId = lastId.substring(2);
			int incrementer = Integer.parseInt(tempId);
			incrementer++;
			uniqueId = new String("si"+incrementer);
		
			
		}
		catch(SQLException sqlE)
		{
			sqlE.printStackTrace();
		}	
		System.out.println("Unique id = "+ uniqueId);
		return uniqueId;
	
	}//end generateStockId()
	
	public void update(StockNew stock)
	{
		try
		{
			this.stock = stock;
			String temp = stock.getStockTypeId();
			String temp1 = convertStockType(temp);
			String temp2 = stock.getCatId();
			String temp5 = convertCatName(temp2);
			String [] str = stock.getStockString();
			PreparedStatement ps = Conn.prepareStatement(
					UPDATE_STOCK);
			
			ps.setString(1, str[0]);
			ps.setString(2, temp1);
			ps.setString(3, str[2]);
			ps.setString(4, str[3]);
			ps.setString(5, str[4]);
			ps.setString(6, str[5]);
			ps.setString(7, str[6]);
			ps.setString(8, str[7]);
			ps.setString(9, temp5);
			ps.setString(10, str[9]);
			ps.setString(11, str[10]);
			ps.setString(12, str[11]);
			ps.setString(13, str[12]);
			ps.setString(14, str[13]);
			ps.setString(15, str[14]);
			ps.setString(16, str[15]);
			ps.setString(17, str[16]);
		
			ps.setString(18, str[0]);
		
			int rowsChanged = ps.executeUpdate();
			System.out.println(rowsChanged);
		}
		catch(SQLException sqlx)
		{
			sqlx.printStackTrace();
		}
	}
	
	public String convertCatName(String inputString)
	{
		String input = inputString;
		try
		{
			PreparedStatement pstmt = Conn.prepareStatement(queryCatId);
			ResultSet rs;
			pstmt.setString(1, input);
			rs = pstmt.executeQuery();
			rs.next();
			String temp3 = rs.getString(1);
			return temp3;	
		}
		catch(SQLException sqle)
		{
			sqle.printStackTrace();
			return "";
		}
		 
	}
	
	public String convertStockType(String inputString)
	{
		String input = inputString;
		try
		{
			PreparedStatement pstmt = Conn.prepareStatement(queryStockType);
			ResultSet rs;
			pstmt.setString(1, input);
			rs = pstmt.executeQuery();
			rs.next();
			String temp4 = rs.getString(1);
			return temp4;	
		}
		catch(SQLException sqle)
		{
			sqle.printStackTrace();
			return "";
		}
		 
	}
}
